﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.IO;
using System.Text;
using System.Windows.Forms;
using System.Windows.Threading;
using DevExpress.Utils.Menu;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Views.Grid;
using ERP_RORZE_DLL.Product.database;
using ERP_RORZE_DLL.Product.DAO;
using System.Data.OleDb;
using ERP_RORZE_PERMISSION.Base;
using ERP_RORZE_SYSTEM;
using System.Data.Common;
using System.Data.SqlClient;
using ERP_RORZE_DLL.Product.Base;
using BaseDAO = ERP_RORZE_TOOL.DBAccess.BaseDAO;
using Utilities = ERP_RORZE_TOOL.Common.Utilities;

namespace ERP_RORZE_PRODUCT.Product.PartData
{
    public partial class ctrlOptionData : DevExpress.XtraEditors.XtraUserControl
    {
        ClsOptionDataTable obj = new ClsOptionDataTable();
        ClsPartDataDAO clspartDAO = new ClsPartDataDAO();
        private LogUtils log;
        DataTable dt;
        private DXPopupMenu updateMenu;

        public ctrlOptionData()
        {
            InitializeComponent();
            InitDateUpdateMenu();
            log = new LogUtils();
            this.dropbtUpdate.DropDownControl = updateMenu;
        }

        void Re_load(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            DataTable dt = clspartDAO.GetAllOptionData();
            dgvOptionData.DataSource = dt;
            //gridView1.BestFitColumns();
            Cursor.Current = Cursors.Default;

        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            Frm_OptionDataAdd frmAdd = new Frm_OptionDataAdd();
            frmAdd.Re_Load += new EventHandler(Re_load);
            frmAdd.Show();
            Cursor.Current = Cursors.Default;
            frmAdd.Activate();
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            try
            {
                Frm_OptionDataEdit frmEdit = new Frm_OptionDataEdit();
                frmEdit.ID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ID").ToString();
                frmEdit.AondNote = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "AondNote").ToString();
                frmEdit.ClampTime = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ClampTime").ToString();
                frmEdit.CLUpdateday = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "CLUpdateday").ToString();
                frmEdit.InputDate = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "InputDate").ToString();
                frmEdit.JigID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "JigID").ToString();
                frmEdit.LastOption = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "LastOption").ToString();
                frmEdit.MachineID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MachineID").ToString();
                frmEdit.Memo = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Memo").ToString();
                frmEdit.Note = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Note").ToString();
                frmEdit.OptionID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "OptionID").ToString();
                frmEdit.PartID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "PartID").ToString();
                frmEdit.ProTime = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ProTime").ToString();
                frmEdit.StaffID = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "StaffID").ToString();
                frmEdit.TechDate = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "TechDate").ToString();
                frmEdit.ToolQty = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ToolQty").ToString();
                frmEdit.UpdateDay = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "UpdateDay").ToString();
                frmEdit.Jig = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "Jig").ToString();
                frmEdit.JigType = gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "JigType").ToString();
                frmEdit.Re_Load += new EventHandler(Re_load);
                frmEdit.Show();
                frmEdit.Activate();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            Cursor.Current = Cursors.Default;
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void dgvOptionData_Load(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {

            if (gridView1.SelectedRowsCount > 0)
            {
                var row = new DataRow[gridView1.SelectedRowsCount];
                var laborId = new string[gridView1.SelectedRowsCount];

                for (int i = 0; i < gridView1.SelectedRowsCount; i++)
                {
                    row[i] = gridView1.GetDataRow(gridView1.GetSelectedRows()[i]);
                    laborId[i] = row[i]["ID"].ToString();
                }


                if (MessageBox.Show("Bạn có thực sự muốn xóa dữ liệu!", "Message", MessageBoxButtons.OKCancel,
                                MessageBoxIcon.Question) == DialogResult.OK)
                {
                    for (int i = 0; i < laborId.Length; i++)
                    {
                        try
                        {
                            obj.ID = int.Parse(laborId[i]);
                            clspartDAO.DeleteOptionData(obj);

                            //write Log
                            log.Table = LogConstant.Table.Product.OptionData;
                            log.Task = LogConstant.Task.Delete;
                            log.Description = "OptionID: " + row[i]["OptionID"] + "; PartID: " + row[i]["PartID"] + "; InputDate: " + row[i]["InputDate"];
                            log.WriteLog();

                        }
                        catch (Exception ex)
                        {

                            MessageBox.Show("Error: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                        }
                    }
                    MessageBox.Show("Đã xóa!");
                    Re_load(sender, e);

                }
            }

        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            Re_load(sender, e);
        }

        private void btnAddNew_Click(object sender, EventArgs e)
        {
            btnAdd_Click(sender, e);
        }

        private void btEdit_Click(object sender, EventArgs e)
        {
            btnEdit_Click(sender, e);
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            btnDelete_Click(sender, e);
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            //FileDialog fileDialog = new OpenFileDialog();
            //fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            //if (fileDialog.ShowDialog() == DialogResult.OK)
            //{
            //    OleDbConnection connection = new OleDbConnection();
            //    try
            //    {
            //        string path = fileDialog.FileName;
            //        INISettings iniSettings = new INISettings();
            //        string connectString = iniSettings.SQLConnectionString;
            //        // Connection String to Excel Workbook
            //        string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

            //        connection.ConnectionString = excelConnectionString;
            //        OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
            //        connection.Open();
            //        // Create DbDataReader to Data Worksheet
            //        DbDataReader dr = command.ExecuteReader();

            //        // Bulk Copy to SQL Server 
            //        SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
            //        bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_OptionData]";

            //        SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("InputDate", "InputDate");
            //        bulkInsert.ColumnMappings.Add(date);

            //        SqlBulkCopyColumnMapping toolId = new SqlBulkCopyColumnMapping("PartID", "PartID");
            //        bulkInsert.ColumnMappings.Add(toolId);

            //        SqlBulkCopyColumnMapping makerId = new SqlBulkCopyColumnMapping("MachineID", "MachineID");
            //        bulkInsert.ColumnMappings.Add(makerId);

            //        SqlBulkCopyColumnMapping PartID = new SqlBulkCopyColumnMapping("OptionID", "OptionID");
            //        bulkInsert.ColumnMappings.Add(PartID);

            //        SqlBulkCopyColumnMapping Qty = new SqlBulkCopyColumnMapping("LastOption", "LastOption");
            //        bulkInsert.ColumnMappings.Add(Qty);

            //        SqlBulkCopyColumnMapping Deadline = new SqlBulkCopyColumnMapping("JigID", "JigID");
            //        bulkInsert.ColumnMappings.Add(Deadline);

            //        SqlBulkCopyColumnMapping RawQty = new SqlBulkCopyColumnMapping("ToolQty", "ToolQty");
            //        bulkInsert.ColumnMappings.Add(RawQty);

            //        SqlBulkCopyColumnMapping HelisertQty = new SqlBulkCopyColumnMapping("ProTime", "ProTime");
            //        bulkInsert.ColumnMappings.Add(HelisertQty);

            //        SqlBulkCopyColumnMapping BlastQty = new SqlBulkCopyColumnMapping("ClampTime", "ClampTime");
            //        bulkInsert.ColumnMappings.Add(BlastQty);

            //        SqlBulkCopyColumnMapping MONo = new SqlBulkCopyColumnMapping("TechDate", "TechDate");
            //        bulkInsert.ColumnMappings.Add(MONo);

            //        SqlBulkCopyColumnMapping MOQty = new SqlBulkCopyColumnMapping("UpdateDay", "UpdateDay");
            //        bulkInsert.ColumnMappings.Add(MOQty);

            //        SqlBulkCopyColumnMapping Started = new SqlBulkCopyColumnMapping("StaffID", "StaffID");
            //        bulkInsert.ColumnMappings.Add(Started);

            //        SqlBulkCopyColumnMapping Finished = new SqlBulkCopyColumnMapping("Note", "Note");
            //        bulkInsert.ColumnMappings.Add(Finished);

            //        SqlBulkCopyColumnMapping FinishDate = new SqlBulkCopyColumnMapping("AondNote", "AondNote");
            //        bulkInsert.ColumnMappings.Add(FinishDate);

            //        SqlBulkCopyColumnMapping Change = new SqlBulkCopyColumnMapping("Memo", "Memo");
            //        bulkInsert.ColumnMappings.Add(Change);

            //        SqlBulkCopyColumnMapping ChangeDate = new SqlBulkCopyColumnMapping("CLUpdateday", "CLUpdateday");
            //        bulkInsert.ColumnMappings.Add(ChangeDate);

            //        SqlBulkCopyColumnMapping Imported = new SqlBulkCopyColumnMapping("JigType", "JigType");
            //        bulkInsert.ColumnMappings.Add(Imported);

            //        SqlBulkCopyColumnMapping ImportFrom = new SqlBulkCopyColumnMapping("Jig", "Jig");
            //        bulkInsert.ColumnMappings.Add(ImportFrom);

            //        bulkInsert.WriteToServer(dr);

            //        connection.Close();
            //        Re_load(sender, e);
            //    }
            //    catch (Exception ex)
            //    {
            //        MessageBox.Show(ex.Message);
            //        connection.Close();
            //    }
            //}

            ImportData();
        }

        private void ImportData()
        {
            INISettings mySetting = new INISettings();
            SqlConnection myConnection = new SqlConnection(mySetting.SQLConnectionString);

            int countOk = 0;
            int countDup = 0;
            int countErr = 0;

            List<Utilities.ListItem> listDup = new List<Utilities.ListItem>();
            List<Utilities.ListItem> listErr = new List<Utilities.ListItem>();
            FileDialog importFile = new OpenFileDialog();
            importFile.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (importFile.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    Control parent = progressPanel1.Parent;
                    progressPanel1.Location =
                        new Point(parent.Bounds.X + parent.Bounds.Width / 2 - progressPanel1.Width / 2,
                                  parent.Bounds.Y + parent.Bounds.Height / 2 - progressPanel1.Height / 2);
                    progressPanel1.Visible = true;
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                   importFile.FileName +
                                                   ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd =
                        new OleDbCommand("Select [InputDate],[PartID],[MachineID],[OptionID] ,[LastOption] " +
                                         ",[JigID] ,[JigType] ,[Jig] ,[ToolQty],[UpdateDay],[ProTime]" +
                                         ",[ClampTime],[TechDate],[StaffID],[Note],[AondNote],[Memo],[CLUpdateday] from [Sheet1$]",
                                         excelConnection);
                    excelConnection.Open();
                    OleDbDataReader dReader;
                    dReader = cmd.ExecuteReader();
                    int i = 0;
                    int line = 1;
                    string lineDup = string.Empty;
                    string lineNo = string.Empty;

                    string inputDate = string.Empty;
                    string updateDay = string.Empty;
                    string cLUpdateday = string.Empty;

                    string PartID = string.Empty;
                    string MachineID = string.Empty;
                    string OptionID = string.Empty;
                    string LastOption = string.Empty;
                    string JigID = string.Empty;
                    string JigType = string.Empty;
                    string Jig = string.Empty;
                    string ToolQty = string.Empty;
                    string ProTime = string.Empty;
                    string ClampTime = string.Empty;
                    string TechDate = string.Empty;
                    string StaffID = string.Empty;
                    string Note = string.Empty;
                    string AondNote = string.Empty;
                    string Memo = string.Empty;


                    myConnection.Open();
                    while (dReader.Read())
                    {
                        bool exist = false;
                        line++;

                        //check null
                        try
                        {
                            if (dReader["InputDate"].ToString().Trim().Equals(string.Empty)
                                && dReader["PartID"].ToString().Trim().Equals(string.Empty)
                                && dReader["MachineID"].ToString().Trim().Equals(string.Empty)
                                && dReader["OptionID"].ToString().Trim().Equals(string.Empty)
                                && dReader["LastOption"].ToString().Trim().Equals(string.Empty)
                                && dReader["JigType"].ToString().Trim().Equals(string.Empty)
                                && dReader["JigID"].ToString().Trim().Equals(string.Empty)
                                && dReader["Jig"].ToString().Trim().Equals(string.Empty)
                                && dReader["ToolQty"].ToString().Trim().Equals(string.Empty)
                                && dReader["UpdateDay"].ToString().Trim().Equals(string.Empty)
                                && dReader["ProTime"].ToString().Trim().Equals(string.Empty)
                                && dReader["ClampTime"].ToString().Trim().Equals(string.Empty)
                                && dReader["TechDate"].ToString().Trim().Equals(string.Empty)
                                && dReader["StaffID"].ToString().Trim().Equals(string.Empty)
                                && dReader["Note"].ToString().Trim().Equals(string.Empty)
                                && dReader["AondNote"].ToString().Trim().Equals(string.Empty)
                                && dReader["Memo"].ToString().Trim().Equals(string.Empty)
                                && dReader["CLUpdateday"].ToString().Trim().Equals(string.Empty)
                                )
                            {
                                countErr++;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"] + " | " + dReader["OptionID"], "Null line"));
                                exist = true;
                            }
                        }
                        catch (Exception e)
                        {
                            countErr++;
                            listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"] + " | " + dReader["OptionID"], e.Message));
                            exist = true;
                        }

                        //check valid data
                        if (!exist)
                        {
                            try
                            {
                                inputDate = (dReader["InputDate"].ToString().Equals(string.Empty))
                                                ? "null"
                                                : "'" + DateTime.Parse(dReader["InputDate"].ToString()).ToString("yyyy/MM/dd") + "'";
                                updateDay = (dReader["UpdateDay"].ToString().Equals(string.Empty))
                                                ? "null"
                                                : "'" + DateTime.Parse(dReader["UpdateDay"].ToString()).ToString("yyyy/MM/dd") + "'";
                                cLUpdateday = (dReader["CLUpdateday"].ToString().Equals(string.Empty))
                                                  ? "null"
                                                  : "'" + DateTime.Parse(dReader["CLUpdateday"].ToString())
                                                            .ToString("yyyy/MM/dd") + "'";

                                PartID = (dReader["PartID"].ToString().Equals(string.Empty))
                                             ? "null"
                                             : "N'" + dReader["PartID"].ToString() + "'";
                                MachineID = (dReader["MachineID"].ToString().Equals(string.Empty))
                                                ? "null"
                                                : "N'" + dReader["MachineID"].ToString() + "'";
                                OptionID = (dReader["OptionID"].ToString().Equals(string.Empty))
                                               ? "null"
                                               : "N'" + dReader["OptionID"].ToString() + "'";
                                LastOption = (dReader["LastOption"].ToString().Equals(string.Empty))
                                                 ? "null"
                                                 : "'" + dReader["LastOption"].ToString() + "'";
                                JigID = (dReader["JigID"].ToString().Equals(string.Empty))
                                            ? "null"
                                            : "N'" + dReader["JigID"].ToString() + "'";
                                JigType = (dReader["JigType"].ToString().Equals(string.Empty))
                                              ? "null"
                                              : "N'" + dReader["JigType"].ToString() + "'";
                                Jig = (dReader["Jig"].ToString().Equals(string.Empty))
                                          ? "null"
                                          : "'" + dReader["Jig"].ToString() + "'";
                                ToolQty = (dReader["ToolQty"].ToString().Equals(string.Empty))
                                              ? "null"
                                              : dReader["ToolQty"].ToString();
                                ProTime = (dReader["ProTime"].ToString().Equals(string.Empty))
                                              ? "null"
                                              : dReader["ProTime"].ToString();
                                ClampTime = (dReader["ClampTime"].ToString().Equals(string.Empty))
                                                ? "null"
                                                : dReader["ClampTime"].ToString();
                                TechDate = (dReader["TechDate"].ToString().Equals(string.Empty))
                                               ? "null"
                                               : "'" + dReader["TechDate"].ToString() + "'";
                                StaffID = (dReader["StaffID"].ToString().Equals(string.Empty))
                                              ? "null"
                                              : "N'" + dReader["StaffID"].ToString() + "'";
                                Note = (dReader["Note"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "N'" + dReader["Note"].ToString() + "'";
                                AondNote = (dReader["AondNote"].ToString().Equals(string.Empty))
                                               ? "null"
                                               : "'" + dReader["AondNote"].ToString() + "'";
                                Memo = (dReader["Memo"].ToString().Equals(string.Empty))
                                           ? "null"
                                           : "N'" + dReader["Memo"].ToString() + "'";

                            }
                            catch (Exception ex)
                            {
                                exist = true;
                                listErr.Add(new Utilities.ListItem(line.ToString(),
                                                                   dReader["PartID"] + " | " + dReader["OptionID"],
                                                                   ex.Message));
                            }
                        }
                        //check OptionID in [242_Option]?
                        if (!exist)
                        {

                            try
                            {
                                SqlCommand cmd_sql = myConnection.CreateCommand();
                                cmd_sql.CommandText = "SELECT * FROM [242_Option] WHERE [OptionID]='" +
                                                      dReader["OptionID"].ToString() + "'";
                                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                                if (!dataReader.Read())
                                {

                                    exist = true;
                                    countErr++;
                                    listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"] + " | " + dReader["OptionID"],
                                                                       "Invalid OptionID"));
                                    //lineNo += line.ToString() + ", ";
                                }
                                dataReader.Close();
                                cmd_sql.Dispose();
                            }
                            catch (Exception ex)
                            {
                                exist = true;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"] + " | " + dReader["OptionID"],
                                                                   ex.Message));
                            }
                        }

                        //check MachineID in [222_Machine]?
                        if (!exist)
                        {

                            try
                            {
                                SqlCommand cmd_sql = myConnection.CreateCommand();
                                cmd_sql.CommandText = "SELECT * FROM [222_Machine] WHERE [MachineID]='" +
                                                      dReader["MachineID"].ToString() + "'";
                                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                                if (!dataReader.Read())
                                {

                                    exist = true;
                                    countErr++;
                                    listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"] + " | " + dReader["OptionID"],
                                                                       "Invalid MachineID"));
                                    //lineNo += line.ToString() + ", ";
                                }
                                dataReader.Close();
                                cmd_sql.Dispose();
                            }
                            catch (Exception ex)
                            {
                                exist = true;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"] + " | " + dReader["OptionID"],
                                                                   ex.Message));
                            }
                        }

                        //check PartID in [242_PartData]?
                        if (!exist)
                        {

                            try
                            {
                                SqlCommand cmd_sql = myConnection.CreateCommand();
                                cmd_sql.CommandText = "SELECT * FROM [242_PartData] WHERE [PartID]='" +
                                                      dReader["PartID"].ToString() + "'";
                                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                                if (!dataReader.Read())
                                {

                                    exist = true;
                                    countErr++;
                                    listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"] + " | " + dReader["OptionID"],
                                                                       "Invalid PartID"));
                                    //lineNo += line.ToString() + ", ";
                                }
                                dataReader.Close();
                                cmd_sql.Dispose();
                            }
                            catch (Exception ex)
                            {
                                exist = true;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"] + " | " + dReader["OptionID"],
                                                                   ex.Message));
                            }
                        }


                        //check duplicate
                        if (!exist)
                        {
                            try
                            {
                                SqlCommand cmd_sql = myConnection.CreateCommand();
                                cmd_sql.CommandText = "SELECT * FROM [242_OptionData] WHERE [OptionID]='" +
                                                      dReader["OptionID"].ToString() + "' and [PartID]='" +
                                                      dReader["PartID"].ToString() + "'";
                                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                                if (dataReader.Read())
                                {

                                    exist = true;
                                    countDup++;
                                    listDup.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"] + " | " + dReader["OptionID"], "Duplicate"));
                                }
                                dataReader.Close();
                                cmd_sql.Dispose();
                            }
                            catch (Exception e)
                            {
                                exist = true;
                                listDup.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"] + " | " + dReader["OptionID"], e.Message));
                            }
                        }

                        //Insert to database
                        if (!exist)
                        {
                            try
                            {
                                string sql = "INSERT INTO [242_OptionData] ("
                                         + "[InputDate],[PartID],[MachineID],[OptionID] ,[LastOption] "
                                         + ",[JigID] ,[JigType] ,[Jig] ,[ToolQty],[UpdateDay],[ProTime]"
                                         + ",[ClampTime],[TechDate],[StaffID],[Note],[AondNote],[Memo],[CLUpdateday] )"
                                         + " VALUES (" + inputDate + ", " + PartID + ", " + MachineID + ","
                                         + OptionID + "," + LastOption + ", " + JigID + ", " + JigType + ", " + Jig + ", "
                                         + ToolQty + " ," + updateDay + ", " + ProTime + ", " + ClampTime + ", " + TechDate + ", " + StaffID + ", "
                                         + Note + ", " + AondNote + ", " + Memo + ", " + cLUpdateday + ")";
                                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);

                                cmd_sql.ExecuteNonQuery();
                                cmd_sql.Dispose();
                                countOk++;

                                //write Log
                                log.Table = LogConstant.Table.Product.OptionData;
                                log.Task = LogConstant.Task.Import;
                                log.Description = "OptionID: " + OptionID + "; PartID: " + PartID + "; InputDate: " + inputDate;
                                log.WriteLog();

                            }
                            catch (Exception ex)
                            {
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["PartID"] + " | " + dReader["OptionID"], ex.Message));
                            }

                        }
                    }
                    myConnection.Close();
                    progressPanel1.Visible = false;
                    excelConnection.Close();
                    //MessageBox.Show(i + " records have been imported. \n" + countDup.ToString() + " record(s) duplicated (Lines: " + lineDup + ")\nLines have error OptionID: " + lineNo);
                    //LoadData();
                    frm_ImportReportOptionData report = new frm_ImportReportOptionData(countOk, listDup, listErr);
                    report.StartPosition = FormStartPosition.CenterScreen;
                    report.Show();
                    report.Activate();
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    progressPanel1.Visible = false;
                    MessageBox.Show(ex.Message);
                }

            }
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            clsConstantsFuction.ExportToExcel(gridView1, 1, true);
        }

        private void gridView1_ValidateRow(object sender, DevExpress.XtraGrid.Views.Base.ValidateRowEventArgs e)
        {
            GridView view = sender as GridView;


            //Kiểm tra đây là dòng dữ liệu mới hay cũ, nếu là mới thì mình insert
            if (view.IsNewItemRow(e.RowHandle))
            {
                //e.RowHandle trả về giá trị int là thứ tự của dòng hiện tại

            }
            //Cũ thì update
            else
            {
                string temUpdateDay = (view.GetRowCellValue(e.RowHandle, view.Columns["UpdateDay"]).ToString().Trim() != string.Empty) ? DateTime.Parse(view.GetRowCellValue(e.RowHandle, view.Columns["UpdateDay"]).ToString()).ToString("yyyy/MM/dd") : view.GetRowCellValue(e.RowHandle, view.Columns["UpdateDay"]).ToString().Trim();
                string temInputDate = (view.GetRowCellValue(e.RowHandle, view.Columns["InputDate"]).ToString().Trim() != string.Empty) ? DateTime.Parse(view.GetRowCellValue(e.RowHandle, view.Columns["InputDate"]).ToString()).ToString("yyyy/MM/dd") : view.GetRowCellValue(e.RowHandle, view.Columns["InputDate"]).ToString().Trim();
                string temCLUpdateday = (view.GetRowCellValue(e.RowHandle, view.Columns["CLUpdateday"]).ToString().Trim() != string.Empty) ? DateTime.Parse(view.GetRowCellValue(e.RowHandle, view.Columns["CLUpdateday"]).ToString()).ToString("yyyy/MM/dd") : view.GetRowCellValue(e.RowHandle, view.Columns["CLUpdateday"]).ToString().Trim();

                string InputDate = (temInputDate == "0001/01/01" || temInputDate == "1900/01/01" || temInputDate.Equals(string.Empty)) ? "null" : "'" + temInputDate + "'";
                string PartID = (view.GetRowCellValue(e.RowHandle, view.Columns["PartID"]).ToString().Trim() == "") ? "null" : "N'" + view.GetRowCellValue(e.RowHandle, view.Columns["PartID"]).ToString() + "'";
                string MachineID = (view.GetRowCellValue(e.RowHandle, view.Columns["MachineID"]).ToString().Trim() == "") ? "null" : "N'" + view.GetRowCellValue(e.RowHandle, view.Columns["MachineID"]).ToString() + "'";
                string OptionID = (view.GetRowCellValue(e.RowHandle, view.Columns["OptionID"]).ToString().Trim() == "") ? "null" : "N'" + view.GetRowCellValue(e.RowHandle, view.Columns["OptionID"]).ToString() + "'";
                string LastOption = (view.GetRowCellValue(e.RowHandle, view.Columns["LastOption"]).ToString().Trim() == "") ? "null" : "N'" + view.GetRowCellValue(e.RowHandle, view.Columns["LastOption"]).ToString() + "'";
                string JigID = (view.GetRowCellValue(e.RowHandle, view.Columns["JigID"]).ToString().Trim() == "") ? "null" : "N'" + view.GetRowCellValue(e.RowHandle, view.Columns["JigID"]).ToString() + "'";
                string ToolQty = (view.GetRowCellValue(e.RowHandle, view.Columns["ToolQty"]).ToString().Trim() == "") ? "null" : view.GetRowCellValue(e.RowHandle, view.Columns["ToolQty"]).ToString();
                string ProTime = (view.GetRowCellValue(e.RowHandle, view.Columns["ProTime"]).ToString().Trim() == "") ? "null" : view.GetRowCellValue(e.RowHandle, view.Columns["ProTime"]).ToString();
                string ClampTime = (view.GetRowCellValue(e.RowHandle, view.Columns["ClampTime"]).ToString().Trim() == "") ? "null" : view.GetRowCellValue(e.RowHandle, view.Columns["ClampTime"]).ToString();
                string TechDate = (view.GetRowCellValue(e.RowHandle, view.Columns["TechDate"]).ToString().Trim() == "") ? "null" : "N'" + view.GetRowCellValue(e.RowHandle, view.Columns["TechDate"]).ToString() + "'";
                string UpdateDay = (temUpdateDay == "0001/01/01" || temUpdateDay == "1900/01/01" || temUpdateDay == "") ? "null" : "'" + temUpdateDay + "'";
                string StaffID = (view.GetRowCellValue(e.RowHandle, view.Columns["StaffID"]).ToString().Trim() == "") ? "null" : "N'" + view.GetRowCellValue(e.RowHandle, view.Columns["StaffID"]).ToString() + "'";
                string Note = (view.GetRowCellValue(e.RowHandle, view.Columns["Note"]).ToString().Trim() == "") ? "null" : "N'" + view.GetRowCellValue(e.RowHandle, view.Columns["Note"]).ToString() + "'";
                string AondNote = (view.GetRowCellValue(e.RowHandle, view.Columns["AondNote"]).ToString().Trim() == "") ? "null" : view.GetRowCellValue(e.RowHandle, view.Columns["AondNote"]).ToString();
                string Memo = (view.GetRowCellValue(e.RowHandle, view.Columns["Memo"]).ToString().Trim() == "") ? "null" : "N'" + view.GetRowCellValue(e.RowHandle, view.Columns["Memo"]).ToString() + "'";
                string CLUpdateday = (temCLUpdateday == "0001/01/01" || temCLUpdateday == "1900/01/01" || temCLUpdateday == "") ? "null" : "'" + temCLUpdateday + "'";
                string JigType = (view.GetRowCellValue(e.RowHandle, view.Columns["JigType"]).ToString().Trim() == "") ? "null" : "N'" + view.GetRowCellValue(e.RowHandle, view.Columns["JigType"]).ToString() + "'";
                string Jig = (view.GetRowCellValue(e.RowHandle, view.Columns["Jig"]).ToString().Trim() == "") ? "null" : "N'" + view.GetRowCellValue(e.RowHandle, view.Columns["Jig"]).ToString() + "'";
                string id = (view.GetRowCellValue(e.RowHandle, view.Columns["ID"]).ToString().Trim() == "") ? "null" : view.GetRowCellValue(e.RowHandle, view.Columns["ID"]).ToString();

                string update = "update [242_OptionData] set [InputDate]=" + InputDate
                    + ", PartID = " + PartID
                    + ", MachineID = " + MachineID
                    + ", [OptionID] = " + OptionID
                    + ", [LastOption] = " + LastOption
                    + ", [JigID] = " + JigID
                    + ", [ToolQty] = " + ToolQty
                    + ", [ProTime] = " + ProTime
                    + ", [ClampTime] = " + ClampTime
                    + ", [TechDate] = " + TechDate
                    + ", [UpdateDay] = " + UpdateDay
                    + ", [StaffID] = " + StaffID
                    + ", [Note] = " + Note
                    + ", [AondNote] = " + AondNote
                    + ", [Memo] = " + Memo
                    + ", [CLUpdateday] = " + CLUpdateday
                    + ", [JigType] = " + JigType
                    + ", [Jig] = " + Jig
                    + " where [ID] = " + id;
                if (BaseDAO.ExecuteNonQuery(update) > 0)
                {
                    //write Log
                    log.Table = LogConstant.Table.Product.OptionData;
                    log.Task = LogConstant.Task.Update;
                    log.Description = "OptionID: " + OptionID + "; PartID: " + PartID + "; InputDate: " + InputDate;
                    log.WriteLog();

                }

            }
        }

        private void gridView1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.Alt)
            {
                if (e.KeyCode == Keys.E)
                {
                    if (gridView1.OptionsBehavior.Editable)
                    {
                        //gridView1.fo;
                        gridView1.OptionsBehavior.Editable = false;
                    }
                    else
                    {
                        gridView1.OptionsBehavior.Editable = true;
                        gridView1.Columns["ID"].OptionsColumn.AllowEdit = false;
                    }
                }
            }
        }

        private void UpdateData(string fileName)
        {
            Cursor.Current = Cursors.WaitCursor;
            List<Utilities.OptionDataUpdateItem> listOk = new List<Utilities.OptionDataUpdateItem>();
            List<Utilities.OptionDataUpdateItem> listErr = new List<Utilities.OptionDataUpdateItem>();
            try
            {
                DataTable dtClone;
                string protime;
                string updateday;
                string staffId;
                string updateSql;
                string partNo;
                string optionId;

                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                       fileName +
                                                       ";Extended Properties=Excel 12.0;Persist Security Info=False";
                OleDbConnection connExcel = new OleDbConnection(excelConnectionString);
                OleDbCommand cmdExcel = new OleDbCommand();
                OleDbDataAdapter oda = new OleDbDataAdapter();
                DataTable dt = new DataTable();
                cmdExcel.Connection = connExcel;

                //Read Data from First Sheet
                connExcel.Open();
                cmdExcel.CommandText = "SELECT * From [Sheet1$]";
                oda.SelectCommand = cmdExcel;
                oda.Fill(dt);
                connExcel.Close();

                ////Bind Data to GridView
                //dgvOptionData.DataSource = dt;
                //gridView1.ViewCaption = Path.GetFileName(fileName);
                //gridView1.PopulateColumns();

                //Get list PartNo + OptionID
                List<Utilities.OptionDataItem> items = new List<Utilities.OptionDataItem>();
                foreach (DataRow row in dt.Rows)
                {
                    bool isAdd = true;
                    Utilities.OptionDataItem optionItem = new Utilities.OptionDataItem(row["PartNo"].ToString(), row["OptionID"].ToString());

                    foreach (Utilities.OptionDataItem item in items)
                    {
                        if (item.PartNo().Equals(optionItem.PartNo()) && item.OptionID().Equals(optionItem.OptionID()))
                        {
                            isAdd = false;
                            break;
                        }
                    }

                    if (isAdd)
                    {
                        items.Add(optionItem);
                    }
                }

                //Get result table to update
                dtClone = dt.Clone();
                foreach (Utilities.OptionDataItem item in items)
                {
                    DataRow[] rowProtime = Utilities.GetMinDataRow(
                            dt.Select("PartNo = '" + item.PartNo() + "' and OptionID='" + item.OptionID() + "'"), "LaborWTS#ProTime", "FLOAT");
                    if (rowProtime.Length == 1)
                    {
                        dtClone.ImportRow(rowProtime[0]);
                    }
                    else if (rowProtime.Length > 1)
                    {
                        DataRow[] temp = Utilities.GetMinDataRow(
                            dt.Select("PartNo = '" + item.PartNo() + "' and OptionID='" + item.OptionID() + "' and [LaborWTS#ProTime]=" + rowProtime[0]["LaborWTS#ProTime"]), "LaborWTS#ProTime", "FLOAT");

                        DataRow[] rowUpdateDay = Utilities.GetMinDataRow(temp, "UpdateDay", "datetime");
                        dtClone.ImportRow(rowUpdateDay[0]);

                    }
                }


                //Update to OptionData
                foreach (DataRow row in dtClone.Rows)
                {
                    protime = row["LaborWTS#ProTime"].ToString();
                    updateday = "'" + row["UpdateDay"].ToString() + "'";
                    staffId = "'" + row["StaffID"].ToString() + "'";
                    partNo = "'" + row["PartNo"].ToString() + "'";
                    optionId = "'" + row["OptionId"].ToString() + "'";

                    updateSql = "Update [242_OptionData] set [ProTime]= cast(" + protime +
                                " as decimal(18,2)) ,[UpdateDay]= " + updateday +
                                " ,[StaffID]=  " + staffId +
                                " where [PartID]=  " + partNo +
                                " and [OptionID]=  " + optionId;
                    if (BaseDAO.ExecuteNonQuery(updateSql) > 0)
                    {
                        listOk.Add(new Utilities.OptionDataUpdateItem(row["PartNo"].ToString(), row["OptionId"].ToString(), row["LaborWTS#ProTime"].ToString(), ((DateTime)row["UpdateDay"]).ToString("MM/dd/yyyy"), row["StaffID"].ToString()));

                        //write Log
                        log.Table = LogConstant.Table.Product.OptionData;
                        log.Task = LogConstant.Task.Update;
                        log.Description = "OptionID: " + optionId + "; PartID: " + partNo + "; UpdateDay: " + updateday;
                        log.WriteLog();

                    }
                    else
                    {
                        listErr.Add(new Utilities.OptionDataUpdateItem(row["PartNo"].ToString(), row["OptionId"].ToString(), row["LaborWTS#ProTime"].ToString(), ((DateTime)row["UpdateDay"]).ToString("MM/dd/yyyy"), row["StaffID"].ToString()));
                    }
                }

                frm_UpdateReportOptionData report = new frm_UpdateReportOptionData(listOk, listErr);
                report.StartPosition = FormStartPosition.CenterScreen;
                report.Show();
                report.Activate();


            }
            catch (Exception ex)
            {
                Cursor.Current = Cursors.Default;
                MessageBox.Show(ex.Message);

                frm_UpdateReportOptionData report = new frm_UpdateReportOptionData(listOk, listErr);
                report.StartPosition = FormStartPosition.CenterScreen;
                report.Show();
                report.Activate();
            }
            Cursor.Current = Cursors.Default;

        }

        private void UpdateDataMachine(string fileName)
        {
            Cursor.Current = Cursors.WaitCursor;

            List<Utilities.OptionDataUpdateItem> listOk = new List<Utilities.OptionDataUpdateItem>();
            List<Utilities.OptionDataUpdateItem> listErr = new List<Utilities.OptionDataUpdateItem>();
            try
            {
                string protime;
                string updateday;
                string clampTime;
                string updateSql;
                string partNo;
                string optionId;

                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                       fileName +
                                                       ";Extended Properties=Excel 12.0;Persist Security Info=False";
                OleDbConnection connExcel = new OleDbConnection(excelConnectionString);
                OleDbCommand cmdExcel = new OleDbCommand();
                OleDbDataAdapter oda = new OleDbDataAdapter();
                DataTable dt = new DataTable();
                cmdExcel.Connection = connExcel;

                //Read Data from First Sheet
                connExcel.Open();
                cmdExcel.CommandText = "SELECT * From [Sheet1$]";
                oda.SelectCommand = cmdExcel;
                oda.Fill(dt);
                connExcel.Close();

                ////Bind Data to GridView
                //dgvOptionData.DataSource = dt;
                //gridView1.ViewCaption = Path.GetFileName(fileName);
                //gridView1.PopulateColumns();

                //Update to OptionData
                foreach (DataRow row in dt.Rows)
                {
                    if (!row["PartNo"].ToString().Trim().Equals("")
                        && !row["Date"].ToString().Trim().Equals("")
                        && !row["OptionID"].ToString().Trim().Equals("")
                        && !row["ProTime"].ToString().Trim().Equals("")
                        && !row["ClampTime"].ToString().Trim().Equals("")
                        )
                    {

                        protime = (row["ProTime"].ToString().Equals(string.Empty)) ? "null" : row["ProTime"].ToString();
                        updateday = (row["Date"].ToString().Equals(string.Empty))
                                        ? "null"
                                        : "'" + row["Date"].ToString() + "'";
                        clampTime = (row["ClampTime"].ToString().Equals(string.Empty))
                                        ? "null"
                                        : "'" + row["ClampTime"].ToString() + "'";
                        partNo = "'" + row["PartNo"].ToString() + "'";
                        optionId = "'" + row["OptionId"].ToString() + "'";

                        updateSql = "Update [242_OptionData] set [ProTime]= cast(" + protime +
                                    " as decimal(18,2)) ,[UpdateDay]= " + updateday +
                                    " ,[ClampTime]=  cast(" + clampTime +
                                    " as decimal(18,2)) ,[Memo]=  null " +
                                    " ,[CLUpdateday]=  null " +
                                    " where [PartID]=  " + partNo +
                                    " and [OptionID]=  " + optionId;
                        if (BaseDAO.ExecuteNonQuery(updateSql) > 0)
                        {
                            listOk.Add(new Utilities.OptionDataUpdateItem(row["PartNo"].ToString(),
                                                                          row["OptionId"].ToString(),
                                                                          row["ProTime"].ToString(),
                                                                          ((DateTime)row["Date"]).ToString("MM/dd/yyyy")));

                            //write Log
                            log.Table = LogConstant.Table.Product.OptionData;
                            log.Task = LogConstant.Task.Update;
                            log.Description = "OptionID: " + optionId + "; PartID: " + partNo + "; UpdateDay: " + updateday;
                            log.WriteLog();

                        }
                        else
                        {
                            listErr.Add(new Utilities.OptionDataUpdateItem(row["PartNo"].ToString(),
                                                                           row["OptionId"].ToString(),
                                                                           row["ProTime"].ToString(),
                                                                           ((DateTime)row["Date"]).ToString(
                                                                               "MM/dd/yyyy")));
                        }
                    }
                }

                frm_UpdateReportOptionData report = new frm_UpdateReportOptionData(listOk, listErr);
                report.StartPosition = FormStartPosition.CenterScreen;
                report.Show();
                report.Activate();


            }
            catch (Exception ex)
            {
                Cursor.Current = Cursors.Default;
                MessageBox.Show(ex.Message);
                frm_UpdateReportOptionData report = new frm_UpdateReportOptionData(listOk, listErr);
                report.StartPosition = FormStartPosition.CenterScreen;
                report.Show();
                report.Activate();
            }
            Cursor.Current = Cursors.Default;

        }

        protected virtual void InitDateUpdateMenu()
        {
            updateMenu = new DXPopupMenu();
            updateMenu.Items.Add(new DXMenuItem("Update nguyên công sau gia công - ProTime", new EventHandler(OnItemClickAfter), null, null, null, null));
            updateMenu.Items.Add(new DXMenuItem("Update nguyên công máy gia công - STG", new EventHandler(OnItemClickMachine), null, null, null, null));

        }

        void OnItemClickAfter(object sender, EventArgs e)
        {
            FileDialog importFile = new OpenFileDialog();
            importFile.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (importFile.ShowDialog() == DialogResult.OK)
            {
                UpdateData(importFile.FileName);
            }
        }

        void OnItemClickMachine(object sender, EventArgs e)
        {
            FileDialog importFile = new OpenFileDialog();
            importFile.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (importFile.ShowDialog() == DialogResult.OK)
            {
                UpdateDataMachine(importFile.FileName);
            }
        }



    }
}
